<?php
class Category_mo extends CI_Model {

	var $title   = '';
	var $content = '';
	var $date    = '';

	function __construct()
	{
		// Call the Model constructor
		parent::__construct();
	}

	function get_all_category($category_code, $display_order)
	{
		if (is_null($category_code))
		{
			$category_code = '0';
		}

		if (is_null($display_order))
		{
			$display_order = 1;
		}

		$sql = 'SELECT ';
		$sql .=     'category_code, ';
		$sql .=     'category_name_pc, ';
		$sql .=     'category_name_mobile, ';
		$sql .=     'parent_category_code, ';
		$sql .=     'path, ';
		$sql .=     'depth, ';
		$sql .=     'display_order, ';
		$sql .=     'commodity_count_pc, ';
		$sql .=     'commodity_count_mobile, ';
		$sql .=     'last_related_count_datetime, ';
		$sql .=     'public_commodity_count_pc, ';
		$sql .=     'public_commodity_count_mobile, ';
		$sql .=     'last_public_count_datetime ';
		$sql .= 'FROM ';
		$sql .=     'category ';
		$sql .= 'WHERE ';
		$sql .=     'path like CONCAT(\'%\', lpad(' . $display_order . ', 4, 0), \':\', ' . $category_code . ', \'%\')';
		$sql .= 'ORDER BY ';
		$sql .=     'path ';

		return $this->db->query($sql)->result();
	}


	function get_sub_category_list($parent_category_code)
	{
		if ($parent_category_code == NULL)
		{
			return NULL;
		}

		$sql = '';
		$sql .= 'SELECT ';
		$sql .=     'category_code, ';
		$sql .=     'parent_category_code, ';
		$sql .=     'category_name_pc, ';
		$sql .=     'display_order ';
		$sql .= 'FROM ';
		$sql .=     'category ';
		$sql .= 'WHERE ';
		$sql .=     'parent_category_code = \'' . $parent_category_code . '\' ';
		$sql .= 'ORDER BY ';
		$sql .=     'display_order';

		return $this->db->query($sql)->result();
	}


	function insert_category($db_param)
	{
		$result = 9;

		try
		{
			if (!isset($db_param))
			{
				return $result = 10;
			}

			$p_parent_category_code = $db_param['parent_category_code'];
			$p_category_code = $db_param['category_code'];
			$p_category_name_pc = $db_param['category_name_pc'];
			$p_category_depth_limit = $db_param['category_depth_limit'];
			$p_path = '';
			$p_depth = 0;
			$p_display_order = 0;

			$this->db->trans_start();

			// get parent data
			$sql = '';
			$sql .= 'SELECT ';
			$sql .=     'category_code, ';
			$sql .=     'path, ';
			$sql .=     'depth + 1 AS depth ';
			$sql .= 'FROM ';
			$sql .=     'category ';
			$sql .= 'WHERE ';
			$sql .=     'category_code = ? ';

			$parent_data = $this->db->query($sql, array($p_parent_category_code));

			if ($parent_data->num_rows() > 0)
			{
			   foreach ($parent_data->result() as $row)
			   {
			      $p_path = $row->path;
			      $p_depth = $row->depth;
			   }
			} else {
				return $result;
			}


			if ($p_depth > $p_category_depth_limit)
			{
				return $result = 11;
			}

			// get max display order
			$sql = '';
			$sql .= 'SELECT ';
			$sql .=     'LPAD(MAX(display_order) + 1, 4, 0)  AS display_order ';
			$sql .= 'FROM ';
			$sql .=     'category ';
			$sql .= 'WHERE ';
			$sql .=     'parent_category_code = ? ';

			$max_display_order = $this->db->query($sql, array($p_parent_category_code));

			if ($max_display_order->num_rows() > 0)
			{
				foreach ($max_display_order->result() as $row)
				{
					$p_display_order = $row->display_order;
				}
			} else {
				return $result;
			}

			if (is_null($p_display_order))
			{
				return $result = 12;
			}

			$param = array();
			$param[] = $p_category_code;
			$param[] = $p_category_name_pc;
			$param[] = $p_parent_category_code;
			$param[] = $p_path . '__' . $p_display_order . ':' . $p_category_code;
			$param[] = $p_depth;
			$param[] = $p_display_order;
			$param[] = 0;
			$param[] = 0;
			$param[] = 0;
			$param[] = 0;
			$param[] = 'admin';
			$param[] = 'admin';

			// insert category
			$sql = '';
			$sql .= 'INSERT INTO category ( ';
			$sql .=     'category_code, ';
			$sql .=     'category_name_pc, ';
			$sql .=     'parent_category_code, ';
			$sql .=     'path, ';
			$sql .=     'depth, ';
			$sql .=     'display_order, ';
			$sql .=     'commodity_count_pc, ';
			$sql .=     'commodity_count_mobile, ';
			$sql .=     'public_commodity_count_pc, ';
			$sql .=     'public_commodity_count_mobile, ';
			$sql .=     'created_user, ';
			$sql .=     'created_datetime, ';
			$sql .=     'updated_user, ';
			$sql .=     'updated_datetime ';
			$sql .= ') VALUES ( ';
			$sql .=     '?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, current_timestamp(), ?, current_timestamp() ';
			$sql .= ') ';

			$is_insert = $this->db->query($sql, $param);
			$this->db->trans_complete();

			if (!$is_insert)
			{
				return $result;
			}

			$result = 0;
			return $result;
		}
		catch (Exception $e)
		{
 			log_message('error', $e->getMessage());
 			$result = 9;
 			return $result;
		}
	}


	function delete_category($db_param)
	{
		$result = 9;

		try
		{
			if (!isset($db_param))
			{
				log_message('error', '');
				return $result = 10;
			}

			$p_category_code = $db_param['category_code'];
			$p_cnt = 0;

			$this->db->trans_start();

			// get count
			$sql = '';
			$sql .= 'SELECT ';
			$sql .=     'COUNT(category_code) AS cnt ';
			$sql .= 'FROM ';
			$sql .=     'category ';
			$sql .= 'WHERE ';
			$sql .=     'commodity_count_pc  > 0 ';
			$sql .=     'AND path like ? ';


			$count = $this->db->query($sql, array('%' . $p_category_code . '%'));

			if ($count->num_rows() > 0)
			{
				foreach ($count->result() as $row)
				{
					$p_cnt = $row->cnt;
				}
			} else {
				return $result;
			}

			// check count
			if ($p_cnt > 0)
			{
				log_message('error', '');
				return $result = 11;
			}

			// get max display order
			$sql = '';
			$sql .= 'DELETE FROM ';
			$sql .=     'category ';
			$sql .= 'WHERE ';
			$sql .=     'path like ? ';

			$is_delete = $this->db->query($sql, array('%' . $p_category_code . '%'));
			$this->db->trans_complete();

			if (!$is_delete)
			{
				return $result;
			}

			$result = 0;
			return $result;
		}
		catch (Exception $e)
		{
			log_message('error', $e->getMessage());
			$result = 9;
			return $result;
		}
	}


	function update_display_order($update_data)
	{
		$result = 9;

// 		try {
			if (is_null($update_data))
			{
				return NULL;
			}

			$sp = 'CALL change_category_priority(?, @result)';
			$sql = 'SELECT @result AS result';

			$this->db->query($sp, array('param'=>$update_data));
			$query = $this->db->query($sql);

			foreach ($query->result_array() as $row)
			{
				$result = $row['result'];
			}
// 		} catch (Exception $e) {
// 			log_message('error', $e->getMessage());
// 			$result = 9;
// 		}

		return $result;
	}


	function get_last_ten_entries()
	{
		$query = $this->db->get('entries', 10);
		return $query->result();
	}

	function insert_entry()
	{
		$this->title   = $_POST['title']; // please read the below note
		$this->content = $_POST['content'];
		$this->date    = time();

		$this->db->insert('entries', $this);
	}

	function update_entry()
	{
		$this->title   = $_POST['title'];
		$this->content = $_POST['content'];
		$this->date    = time();

		$this->db->update('entries', $this, array('id' => $_POST['id']));
	}

}